package de.fhb.itunes.repository.DataAccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import de.fhb.itunes.repository.WrongVoMap;
import de.fhb.itunes.repository.data.Episode_VO;
import de.fhb.itunes.repository.data.Film_VO;
import de.fhb.itunes.repository.data.TvSendung_VO;
import de.fhb.itunes.repository.jdbc.DataAccessException;
import de.fhb.itunes.repository.jdbc.JdbcDAOBase;

public class DAO_TvSendung extends JdbcDAOBase implements IDAO_TvSendung {

	@Override
	public List<TvSendung_VO> getAll() {
		List<TvSendung_VO> vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT Distinct Staffelname, Staffelnummer FROM  [MediaFileSatz] medi, [MediaFileSatz_Episode] epi WHERE medi.[ID] = epi.[ID] AND epi.istTVSendung = 1";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		

		return vos;
	}

	@Override
	public List<TvSendung_VO> getbyStaffelname(String staffel) {
		List<TvSendung_VO> vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int key = -1;

		String sqlStr = "SELECT Distinct Staffelname, Staffelnummer  FROM  [MediaFileSatz] medi,   [MediaFileSatz_Episode] epi WHERE medi.[ID] = epi.[ID] AND epi.istTVSendung = 1 AND epi.Staffelname = ?";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, staffel);
			rs = ps.executeQuery();
			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public TvSendung_VO getbyStaffelnameAndNummer(String staffel, int nummer) {
		TvSendung_VO vo;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int key = -1;

		String sqlStr = "SELECT Distinct Staffelname, Staffelnummer  FROM  [MediaFileSatz] medi,   [MediaFileSatz_Episode] epi WHERE medi.[ID] = epi.[ID] AND epi.istTVSendung = 1 AND epi.Staffelname = ? AND epi.Staffelnummer = ?";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, staffel);
			ps.setInt(2, nummer);
			rs = ps.executeQuery();
			vo = (TvSendung_VO) transformSetToVO(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vo;
	}


	@Override
	protected Object map2VO(ResultSet rs) throws SQLException {
		TvSendung_VO tv = new TvSendung_VO();
		tv.setEpisoden(getEpisodenForTVSendung(rs.getString("Staffelname"), rs.getInt("Staffelnummer")));
		tv.setStaffelname(rs.getString("Staffelname"));
		tv.setStaffelnummer(rs.getInt("Staffelnummer"));
		return tv;
	}
	private HashMap<Integer, Episode_VO> getEpisodenForTVSendung(String staffel, int nummer){
		IDAO_Episode episodenverwaltungEpisode = new DAO_Episode();
		HashMap<Integer, Episode_VO> episoden = episodenverwaltungEpisode
				.getByStaffelAndStaffelnummer(staffel,nummer);
		return episoden;
	}

}
